
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/13/2011 23:36:18
-- Generated from EDMX file: D:\RouteTiming\RouteTiming.Data\RouteTimingModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ROUTE_TIMING];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_RouteRouteSchedule_Route]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RouteRouteSchedule] DROP CONSTRAINT [FK_RouteRouteSchedule_Route];
GO
IF OBJECT_ID(N'[dbo].[FK_RouteRouteSchedule_RouteSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RouteRouteSchedule] DROP CONSTRAINT [FK_RouteRouteSchedule_RouteSchedule];
GO
IF OBJECT_ID(N'[dbo].[FK_RoutePointEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EventSet] DROP CONSTRAINT [FK_RoutePointEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EventSet] DROP CONSTRAINT [FK_PersonEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_RouteRoutePoint]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RoutePointSet] DROP CONSTRAINT [FK_RouteRoutePoint];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonRouteSchedule_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonRouteSchedule] DROP CONSTRAINT [FK_PersonRouteSchedule_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonRouteSchedule_RouteSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonRouteSchedule] DROP CONSTRAINT [FK_PersonRouteSchedule_RouteSchedule];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[RoutePointSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RoutePointSet];
GO
IF OBJECT_ID(N'[dbo].[RouteSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RouteSet];
GO
IF OBJECT_ID(N'[dbo].[PersonSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonSet];
GO
IF OBJECT_ID(N'[dbo].[EventSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EventSet];
GO
IF OBJECT_ID(N'[dbo].[RouteScheduleSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RouteScheduleSet];
GO
IF OBJECT_ID(N'[dbo].[RouteRouteSchedule]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RouteRouteSchedule];
GO
IF OBJECT_ID(N'[dbo].[PersonRouteSchedule]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonRouteSchedule];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'RoutePointSet'
CREATE TABLE [dbo].[RoutePointSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(256)  NOT NULL,
    [OrderNo] int  NOT NULL,
    [PassInterval] nvarchar(max)  NOT NULL,
    [RouteId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'RouteSet'
CREATE TABLE [dbo].[RouteSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PersonSet'
CREATE TABLE [dbo].[PersonSet] (
    [Id] uniqueidentifier  NOT NULL,
    [FirstName] nvarchar(256)  NOT NULL,
    [MiddleName] nvarchar(256)  NOT NULL,
    [LastName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'EventSet'
CREATE TABLE [dbo].[EventSet] (
    [Id] uniqueidentifier  NOT NULL,
    [OrderNo] bigint IDENTITY(1,1) NOT NULL,
    [TimeRaised] datetime  NOT NULL,
    [TimeAdded] datetime  NOT NULL,
    [Desc] nvarchar(max)  NULL,
    [EventTypeId] int  NOT NULL,
    [RoutePointId] uniqueidentifier  NULL,
    [ParticipantId] uniqueidentifier  NULL
);
GO

-- Creating table 'RouteScheduleSet'
CREATE TABLE [dbo].[RouteScheduleSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [StartTime] datetime  NOT NULL
);
GO

-- Creating table 'RouteRouteSchedule'
CREATE TABLE [dbo].[RouteRouteSchedule] (
    [Routes_Id] uniqueidentifier  NOT NULL,
    [RouteRouteSchedule_Route_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'PersonRouteSchedule'
CREATE TABLE [dbo].[PersonRouteSchedule] (
    [Persons_Id] uniqueidentifier  NOT NULL,
    [PersonRouteSchedule_Person_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'RoutePointSet'
ALTER TABLE [dbo].[RoutePointSet]
ADD CONSTRAINT [PK_RoutePointSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'RouteSet'
ALTER TABLE [dbo].[RouteSet]
ADD CONSTRAINT [PK_RouteSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PersonSet'
ALTER TABLE [dbo].[PersonSet]
ADD CONSTRAINT [PK_PersonSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [PK_EventSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'RouteScheduleSet'
ALTER TABLE [dbo].[RouteScheduleSet]
ADD CONSTRAINT [PK_RouteScheduleSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Routes_Id], [RouteRouteSchedule_Route_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [PK_RouteRouteSchedule]
    PRIMARY KEY NONCLUSTERED ([Routes_Id], [RouteRouteSchedule_Route_Id] ASC);
GO

-- Creating primary key on [Persons_Id], [PersonRouteSchedule_Person_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [PK_PersonRouteSchedule]
    PRIMARY KEY NONCLUSTERED ([Persons_Id], [PersonRouteSchedule_Person_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Routes_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [FK_RouteRouteSchedule_Route]
    FOREIGN KEY ([Routes_Id])
    REFERENCES [dbo].[RouteSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [RouteRouteSchedule_Route_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [FK_RouteRouteSchedule_RouteSchedule]
    FOREIGN KEY ([RouteRouteSchedule_Route_Id])
    REFERENCES [dbo].[RouteScheduleSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RouteRouteSchedule_RouteSchedule'
CREATE INDEX [IX_FK_RouteRouteSchedule_RouteSchedule]
ON [dbo].[RouteRouteSchedule]
    ([RouteRouteSchedule_Route_Id]);
GO

-- Creating foreign key on [RoutePointId] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [FK_RoutePointEvent]
    FOREIGN KEY ([RoutePointId])
    REFERENCES [dbo].[RoutePointSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoutePointEvent'
CREATE INDEX [IX_FK_RoutePointEvent]
ON [dbo].[EventSet]
    ([RoutePointId]);
GO

-- Creating foreign key on [ParticipantId] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [FK_PersonEvent]
    FOREIGN KEY ([ParticipantId])
    REFERENCES [dbo].[PersonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonEvent'
CREATE INDEX [IX_FK_PersonEvent]
ON [dbo].[EventSet]
    ([ParticipantId]);
GO

-- Creating foreign key on [RouteId] in table 'RoutePointSet'
ALTER TABLE [dbo].[RoutePointSet]
ADD CONSTRAINT [FK_RouteRoutePoint]
    FOREIGN KEY ([RouteId])
    REFERENCES [dbo].[RouteSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RouteRoutePoint'
CREATE INDEX [IX_FK_RouteRoutePoint]
ON [dbo].[RoutePointSet]
    ([RouteId]);
GO

-- Creating foreign key on [Persons_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [FK_PersonRouteSchedule_Person]
    FOREIGN KEY ([Persons_Id])
    REFERENCES [dbo].[PersonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [PersonRouteSchedule_Person_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [FK_PersonRouteSchedule_RouteSchedule]
    FOREIGN KEY ([PersonRouteSchedule_Person_Id])
    REFERENCES [dbo].[RouteScheduleSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonRouteSchedule_RouteSchedule'
CREATE INDEX [IX_FK_PersonRouteSchedule_RouteSchedule]
ON [dbo].[PersonRouteSchedule]
    ([PersonRouteSchedule_Person_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------